<?php

class Promotion extends Model
{
	var $id;
	var $year;
	var $director;
	var $created_at;
	var $updated_at;

	function Promotion()
	{
		parent::Model();
	}

	function get_by_id($id)
	{
		$this->db->select('p.id, p.year, p.director, p.created_at, p.updated_at')
		         ->from('promotion p')
		         ->where('p.id', $id);
		$query = $this->db->get();
		return $query->row();
	}

	function all()
	{
		$sql = '
		select p.id, p.year, p.director, p.created_at, (
			select count(*)
			from user u
			left join insc_entry e on e.user_id = u.id
			left join insc_field f on f.id = e.field_id
			where f.field_id = "promotion"
			and e.value_int = p.year
			and u.active = 1
		) as user_count
		from promotion p
		order by year';
		$query = $this->db->query($sql);
		return $query->result();
	}


	function get_current()
	{
		$sql = 'select p.id, p.year, p.director, p.created_at, p.updated_at
		       from promotion p
		       where p.year = (select max(p2.year) from promotion p2)';
		$query = $this->db->query($sql);
		return $query->row();
	}

	/**
	 * Cloturer l'année scolaire en cours et ouvrir la prochaine promotion
	 */
	function active_next()
	{
		$success = true;
		// création de la promotion de l'année suivante
		$sql = 'insert into promotion (id, year, created_at, updated_at)
		 values (NULL, (select max(p.year) + 1 from promotion p), NOW(), NOW())';
		$success &= $this->db->query($sql);

		// suppression des utilisateurs dont le statut n'est ni admin, ni main_list, ni former_student
		$sql = 'update user
		 set active = 0
		 where status_id not in (select id
		                         from user_status
		                         where code in ("admin", "main_list", "former_student"))';
		$success &= $this->db->query($sql);

		// les utilisateurs de statut main_list passent en statut former_student
		$sql = 'update user
		 set status_id = (select id
		                  from user_status
		                  where code = "former_student")
		 where status_id in (select id
		                     from user_status
		                     where code = "main_list")';
		$success &= $this->db->query($sql);
		return $success;
	}
}
